﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MaxLeaf.Platform.Contract;
using MaxLeaf.Platform.DataContract;
using CommonsLib.DB;
using System.Transactions;
using System.Data.SqlClient;
using System.Data.Common;
using System.Diagnostics;
using CommonsLib.Settings;
using System.Data;

using System.Security.Cryptography;
using MaxLeaf.Platform.Functions;
using MaxLeaf.Platform.Commons;
using CommonsLib.Utility;

namespace MaxLeaf.Platform.DALImplementation
{
    public class ImInvMasterDAL :BaseDAL 
    {



        public double GetWebPrice(DBAccess dbcon, int intGoodsID, int intUnitID, int intAreaID, int intUserID, int intPriceType)
        {
            string strSql = string.Empty;
            if (intPriceType == 1)
            {
                strSql = "select dbo.F_GetWebPrice(@GoodsID,@UnitID,@UserID,@AreaID )";
            }
            else
            {
                strSql="select dbo.F_GetWebMarketPrice(@GoodsID,@UnitID)";
            }
            return CvtFunc.ToDouble( dbcon.GetValue(strSql,new SqlParameter[]
                {
                    new SqlParameter("@GoodsID",intGoodsID.ToString())
                    ,new SqlParameter("@UnitID",intUnitID.ToString())
                    ,new SqlParameter("@AreaID",intAreaID.ToString())
                    ,new SqlParameter("@UserID",intUserID.ToString())
                }
                ));
        }


        public double GetRate(DBAccess dbcon, int intGoodsID, int intToUnitID)
        {
            string strStockUnitID = dbcon.GetValue("select stockunitid from iminvmaster where GoodsID='" + intGoodsID + "'");
            string strSQL = "select dbo.F_UnitCnv( " + intGoodsID + " , 1 ,  " + strStockUnitID + ", " + intToUnitID + ") ";            
            double dblRate = CvtFunc.ToDouble(dbcon.GetValue(strSQL));
            //if (dblRate == 0) dblRate = 1;
            return dblRate; 
        }

        

        public DbDataReader GetNoInvLocItems(DBAccess dbcon, int intCompanyID, int intGoodsID)
        {
            string strSql = string.Format(@"select  * from ImInvMaster where {0}
                    and not exists (select a.* from {1}IminvLoc a where a.ItemId=ImInvMaster.finterid and a.Companyid=@Companyid)"
                     , intGoodsID > 0 ? " GoodsID=@GoodsID" : "1=1"
                     , GetInvDatabase()
                    );


            return dbcon.GetDataReader(strSql, new SqlParameter[]
                        {
                            new SqlParameter("@Companyid",intCompanyID.ToString())
                            ,new SqlParameter("@GoodsID",intGoodsID.ToString())
                    
                        }
                      );
        }



        internal static string NoInvTableName(object model)
        {
            int intGoodsID = CvtFunc.ToInt(DataFunc.GetPropValue(model, "UI_GoodsID"));
            string strSql = string.Format(@"
                (
                    select  i.*
                    ,dbo.F_GetSCPCategoryID({2},i.goodsid,1) as SCPCategoryTopId
                    ,g.goods_name as GoodsName
                    ,dbo.F_GetWebPrice(i.goodsid,i.stockunitid,{3},{4}) as WebContractPrice
                    ,isnull(dbo.F_GetItemAttrValue(i.finterid),'') as GoodsAttrValue
                    from ImInvMaster i inner join goods_main g on i.goodsid=g.goods_id and g.is_delete=0 and g.is_on_sale=1  and i.is_delete=0  and i.Is_UsedAttr=1
                    where {0}
                    and not exists (select a.* from {1}IminvLoc a where a.ItemId=i.finterid and a.Companyid={2})
                ) a"
                     , intGoodsID > 0 ? string.Format(" GoodsID={0} " , intGoodsID) : "1=1"
                     , GetInvDatabase()
                    , DataFunc.GetPropValue(model, "UI_CompanyID")
                    , DataFunc.GetPropValue(model, "UI_UserID")
                    , DataFunc.GetPropValue(model, "UI_AreaID")
                    );

            return strSql;    
        }

        internal static string ItemQryTableName(object model)
        {
            using (DBAccess dbcon = DataFunc.GetDBAccessByDataModelType(typeof(ImInvMasterDataModel)))
            {
                dbcon.ExecuteSql( string.Format(@"insert into ImWebContractPrice(GoodsID,UnitID,AreaID,WebUserID,WebContractPrice)

select distinct i.GoodsID,{2},{1},dbo.F_GetWebUserID({0}),isnull(dbo.F_GetWebPrice(i.GoodsID,{2},{0},{1} ),0) from ImInvMaster i inner join goods_main g on i.goodsid=g.goods_id 
where not exists(select GoodsID from ImWebContractPrice where GoodsID=i.GoodsID and  UnitID={2} and  AreaID={1} and  WebUserID={3})
  "
                    , DataFunc.GetPropValue(model, "UI_UserID")
                    , DataFunc.GetPropValue(model, "UI_AreaID")
                    , CvtFunc.ToInt(DataFunc.GetPropValue(model, "UI_UnitID")) > 0 ? DataFunc.GetPropValue(model, "UI_UnitID") : "i.stockunitid"
                    , dbcon.GetValue(string.Format("select dbo.F_GetWebUserID({0}) ", DataFunc.GetPropValue(model, "UI_UserID")))
                    )
                    );
            }
            string strSql = string.Format(@"
                (
                    select  i.finterid as ItemID,i.GoodsID,i.StockUnitID,i.GoodsAttrID,i.is_delete
                    ,isnull(dbo.F_GetItemAttrValue(i.finterid),'') as GoodsAttrValue
                    ,isnull(catTop.cat_id,0) as GoodsCategoryTopId,isnull(catTop.cat_name,'') as GoodsCategoryTopName
                    ,isnull(catCur.cat_id,0) as GoodsCategoryCurId,isnull(catCur.cat_name,'') as GoodsCategoryCurName
                    ,isnull(scpTop.finterid,0) as SCPCategoryTopId,isnull(scpTop.SCPCategoryName,'') as SCPCategoryTopName
                    ,isnull(scpCur.finterid,0) as SCPCategoryCurId,isnull(scpCur.SCPCategoryName,'') as SCPCategoryCurName
                    ,isnull(scpCur.SCPCategorySeqNo,0) as SCPCategorySeqNo
                    ,g.goods_name as GoodsName,g.sku_num,g.spec
                    , isnull(dbo.F_CheckContractItem(i.goodsid,{3},{1},{2}),0)  as Is_ContractItem
                    ,isnull(dbo.F_GetWebContractPrice(i.goodsid,{3},{1},{2}),0) as WebContractPrice
                    ,isnull(dbo.F_GetWebMarketPrice(i.goodsid,{3}),0) as WebMarketPrice
                    ,isnull(goods_UserKeywords.usegoods_name,'') as usegoods_name,isnull(goods_UserKeywords.usegoods_id,0) as usegoods_id
                    ,isnull(g_b.brand_id,0) as brand_id,isnull(g_b.brand_name,'') as brand_name
                    ,dbo.F_ToDateTime(g.update_time)  as GoodsUpdateTime
                    ,dbo.F_GetItemImgURL(i.finterid,'')  as ImgURL
                    ,dbo.F_GetItemImgURL(i.finterid,'small')  as ImgURL_Small
                    ,dbo.F_GetItemImgURL(i.finterid,'large')  as ImgURL_Large
                    ,dbo.F_GetItemImgURL(i.finterid,'detail')  as ImgURL_Detail
                    ,g.saving_index

                    from ImInvMaster i inner join goods_main g on i.goodsid=g.goods_id  and g.is_delete=0 and g.is_on_sale=1 and i.is_delete=0 and i.Is_UsedAttr=1
                    left join goods_brand g_b on g_b.brand_id=g.brand_id
                    left join goods_category catTop on  catTop.cat_id=dbo.F_GetGoodsCategoryID(i.goodsid,1)
                    left join goods_category catCur on  catCur.cat_id=dbo.F_GetGoodsCategoryID(i.goodsid,0)
                    left join SCPCategory scpTop on scpTop.CompanyID={0} and  scpTop.finterid=dbo.F_GetSCPCategoryID({0},i.goodsid,1)
                    left join SCPCategory scpCur on scpCur.CompanyID={0} and  scpCur.finterid=dbo.F_GetSCPCategoryID({0},i.goodsid,0)
                    left join goods_UserKeywords on goods_UserKeywords.Company_ID={0} and  goods_UserKeywords.Itemid=i.finterid
                    
                ) a"

                    , DataFunc.GetPropValue(model, "UI_CompanyID")
                    , DataFunc.GetPropValue(model, "UI_UserID")
                    , DataFunc.GetPropValue(model, "UI_AreaID")
                    , CvtFunc.ToInt(DataFunc.GetPropValue(model, "UI_UnitID")) > 0 ?  DataFunc.GetPropValue(model, "UI_UnitID") : "i.stockunitid"
                    );

            return strSql;    
        }

        public DbDataReader GetGoodCateGoryBySCPCategory(DBAccess dbcon, int intCompanyID, int intSCPCategoryID)
        {
            string strSql = "exec P_GetGoodCateGoryBySCPCategory @CompanyID,@SCPCategoryID ";


            return dbcon.GetDataReader(strSql, new SqlParameter[]
                        {
                            new SqlParameter("@Companyid",intCompanyID.ToString())
                            ,new SqlParameter("@SCPCategoryID",intSCPCategoryID.ToString())
                    
                        }
                      );
        }

        internal static string ItemBaseInfoQryTableName(object model)
        {
            string strSql = @"
                (
                    select  i.finterid as ItemID
                    ,isnull(dbo.F_GetItemAttrValue(i.finterid),'') as GoodsAttrValue                    
                    from ImInvMaster i
                ) a";

            return strSql;    
        }
    }
}
